﻿using System;
using System.Linq.Expressions;
using System.Text.RegularExpressions;

namespace Dapper.Library.DapperPostgreSql
{
    public class PostgreSqlProvider : SqlProvider
	{
		private readonly static string OpenQuote = "\"";
		private readonly static string CloseQuote = "\"";
		private readonly static char ParameterPrefix = '@';
		private IResolveExpression ResolveExpression;
		public PostgreSqlProvider()
		{
			ProviderOption = new ProviderOption(OpenQuote, CloseQuote, ParameterPrefix);
			ResolveExpression = new ResolveExpression(this);
		}

		public sealed override IProviderOption ProviderOption { get; set; }

		public override SqlProvider FormatToSingle<T>()
		{
			if (!string.IsNullOrWhiteSpace(Context.Set.StringSql))
			{
                var whereSql = ResolveExpression.ResolveWhereList();
                if (string.IsNullOrEmpty(whereSql) || whereSql.Equals("WHERE 1=1 "))
                {
                    SqlString = $"{Context.Set.StringSql}";
                }
                else
                {
                    var asName = Context.Set.TableType.Name;
                    SqlString = $"SELECT * FROM ({Context.Set.StringSql}) AS {asName} {whereSql}";
                }
			}
			else
			{
				var selectSql = ResolveExpression.ResolveSelect(null);

				var fromTableSql = FormatTableName();

				var whereSql = ResolveExpression.ResolveWhereList();

				var joinSql = ResolveExpression.ResolveJoinSql(JoinList, ref selectSql);

				var groupSql = ResolveExpression.ResolveGroupBy();

				var havingSql = ResolveExpression.ResolveHaving();

				var orderbySql = ResolveExpression.ResolveOrderBy(DBType.PostgreSql);

				SqlString = $@"SELECT T.* FROM( 
                            {selectSql}
                            {fromTableSql} {joinSql}
                            {whereSql}
                            {groupSql}
                            {havingSql}
                            {orderbySql}
                            ) T
                            LIMIT 1 OFFSET 0";
			}

			return this;
		}

		public override SqlProvider FormatToList<T>()
		{
			if (!string.IsNullOrWhiteSpace(Context.Set.StringSql))
			{
                var whereSql = ResolveExpression.ResolveWhereList();
                if (string.IsNullOrEmpty(whereSql) || whereSql.Equals("WHERE 1=1 "))
                {
                    SqlString = $"{ Context.Set.StringSql}";
                }
                else
                {
                    var asName = Context.Set.TableType.Name;
                    SqlString = $"SELECT * FROM ({Context.Set.StringSql}) AS {asName} {whereSql}";
                }
			}
			else
			{
				var selectSql = ResolveExpression.ResolveSelect(null);

				var fromTableSql = FormatTableName();

				var whereSql = ResolveExpression.ResolveWhereList();

				var joinSql = ResolveExpression.ResolveJoinSql(JoinList, ref selectSql);

				var groupSql = ResolveExpression.ResolveGroupBy();

				var havingSql = ResolveExpression.ResolveHaving();

				var orderbySql = ResolveExpression.ResolveOrderBy(DBType.PostgreSql);

				SqlString = $"{selectSql} {fromTableSql} {joinSql} {whereSql} {groupSql} {havingSql} {orderbySql}";
			}

			return this;
		}


		public override SqlProvider FormatToPageList<T>(int pageIndex, int pageSize)
		{
			if (!string.IsNullOrWhiteSpace(Context.Set.StringSql))
			{
				var orderbySql = ResolveExpression.ResolveOrderBy(DBType.PostgreSql);

				//if (string.IsNullOrEmpty(orderbySql))
				//{
				//	throw new Exception("排序方式优先于页列表");
				//}

                var whereSql = ResolveExpression.ResolveWhereList();
                if (string.IsNullOrEmpty(whereSql)|| whereSql.Equals("WHERE 1=1 "))
                {
                    SqlString = $@"{Context.Set.StringSql} {orderbySql}  
                                    LIMIT  {pageSize} OFFSET {((pageIndex - 1) * pageSize)};";
                }
                else
                {
                    var asName = Context.Set.TableType.Name;
                    SqlString = $@"SELECT * FROM ({ Context.Set.StringSql} {orderbySql} ) AS {asName} {whereSql}
                                     LIMIT  {pageSize} OFFSET {((pageIndex - 1) * pageSize)};";
                }
            }
			else
			{
				var orderbySql = ResolveExpression.ResolveOrderBy(DBType.PostgreSql);

				//Oracle可以不用必须排序翻页
				//if (string.IsNullOrEmpty(orderbySql))
				//    throw new DapperExtensionException("order by takes precedence over pagelist");

				var selectSql = ResolveExpression.ResolveSelect(null);

				var fromTableSql = FormatTableName();

				var whereSql = ResolveExpression.ResolveWhereList();

				var joinSql = ResolveExpression.ResolveJoinSql(JoinList, ref selectSql);

				var groupSql = ResolveExpression.ResolveGroupBy();

				var havingSql = ResolveExpression.ResolveHaving();

				SqlString = $@" SELECT 
                           {(new Regex("SELECT").Replace(selectSql, "", 1))}
                            {fromTableSql} {joinSql} {whereSql} {groupSql} {havingSql} {orderbySql}
                           LIMIT  {pageSize} OFFSET {((pageIndex - 1) * pageSize)};";
			}
			return this;
		}

		public override SqlProvider FormatCount()
		{
			if (!string.IsNullOrWhiteSpace(Context.Set.StringSql))
			{
                var whereSql = ResolveExpression.ResolveWhereList();
                if (string.IsNullOrEmpty(whereSql) || whereSql.Equals("WHERE 1=1 "))
                {
                    var executeSql = $"SELECT COUNT(1) FROM ({Context.Set.StringSql}) ForCountInfo";
                            SqlString = $"{executeSql}";
                }
                else
                {
                    var asName = Context.Set.TableType.Name;
                    SqlString = $@"SELECT COUNT(1) FROM ({Context.Set.StringSql}) AS {asName} {whereSql}";
                }
			}
			else
			{
				var selectSql = "SELECT COUNT(1)";

				var fromTableSql = FormatTableName();

				string noneSql = "";
				var joinSql = ResolveExpression.ResolveJoinSql(JoinList, ref noneSql);

				var whereSql = ResolveExpression.ResolveWhereList();

				SqlString = $"{selectSql} {fromTableSql} {joinSql} {whereSql} ";
			}

			return this;
		}

		public override SqlProvider FormatDelete()
		{
			var fromTableSql = FormatTableName(false, false);

			ProviderOption.IsAsName = false;

			var whereSql = ResolveExpression.ResolveWhereList();

			SqlString = $"DELETE FROM {fromTableSql} {whereSql}";

			return this;
		}

		/// <summary>
		/// 获取ExecuteNoQuerySQ
		/// </summary>
		/// <returns></returns>
		public override SqlProvider FormatExecuteNoQuery()
		{
			//DynamicParameters parameters = new DynamicParameters();
			//parameters.AddDynamicParams(Context.Set.Params);
			//Params = parameters;

			var executeSql = Context.Set.StringSql;
			SqlString = $"{executeSql}";
			return this;
		}


		public override SqlProvider FormatInsert<T>(T entity, string[] excludeFields)
		{
			var paramsAndValuesSql = FormatInsertParamsAndValues(entity, excludeFields);

			if (Context.Set.IfNotExistsList.Count==0)
			{
				SqlString = $"INSERT INTO {FormatTableName(false, false)} ({paramsAndValuesSql[0]}) VALUES({paramsAndValuesSql[1]})";
			}	
			else
			{
				string ifNotExistsStr=  ResolveExpression.ResolveIfNotExists();
				SqlString = $"INSERT INTO {FormatTableName(false, false)}({paramsAndValuesSql[0]}) VALUES({paramsAndValuesSql[1]}) ON conflict({ifNotExistsStr}) DO NOTHING;";
			}
			return this;
		}

		public override SqlProvider FormatInsertIdentity<T>(T entity, string[] excludeFields)
		{
			var paramsAndValuesSql = FormatInsertParamsAndValues(entity, excludeFields);
			SqlString = $"INSERT INTO {FormatTableName(false, false)} ({paramsAndValuesSql[0]}) VALUES({paramsAndValuesSql[1]}); SELECT @@IDENTITY";
			return this;
		}

		public override SqlProvider FormatUpdate<T>(Expression<Func<T, T>> updateExpression)
		{
			var update = ResolveExpression.ResolveUpdate(updateExpression);

			ProviderOption.IsAsName = false;

			var whereSql = ResolveExpression.ResolveWhereList();
			Params.AddDynamicParams(update.Param);

			SqlString = $"UPDATE {FormatTableName(false, false)} {update.SqlCmd} {whereSql}";

			return this;
		}

		public override SqlProvider FormatUpdate<T>(T entity, string[] excludeFields, bool isBatch = false)
		{
			var update = ResolveExpression.ResolveUpdates<T>(entity, Params, excludeFields);

			ProviderOption.IsAsName = false;

			var whereSql = ResolveExpression.ResolveWhereList();
			//如果不存在条件，就用主键作为条件
			if (!isBatch)
				if (whereSql.Trim().Equals("WHERE 1=1"))
					whereSql += GetIdentityWhere(entity, Params);

			SqlString = $"UPDATE {FormatTableName(false, false)} {update} {whereSql}";
			return this;
		}

		public override SqlProvider FormatSum(LambdaExpression sumExpression)
		{
			var selectSql = ResolveExpression.ResolveSum(sumExpression);

			var fromTableSql = FormatTableName();

			var whereSql = ResolveExpression.ResolveWhereList();

			string noneSql = "";
			var joinSql = ResolveExpression.ResolveJoinSql(JoinList, ref noneSql);

			SqlString = $"{selectSql} {fromTableSql}{joinSql} {whereSql} ";

			return this;
		}

		public override SqlProvider FormatMin(LambdaExpression minExpression)
		{
			var selectSql = ResolveExpression.ResolveMin(minExpression);

			var fromTableSql = FormatTableName();

			var whereSql = ResolveExpression.ResolveWhereList();

			string noneSql = "";
			var joinSql = ResolveExpression.ResolveJoinSql(JoinList, ref noneSql);

			SqlString = $"{selectSql} {fromTableSql}{joinSql} {whereSql} ";

			return this;
		}

		public override SqlProvider FormatMax(LambdaExpression maxExpression)
		{
			var selectSql = ResolveExpression.ResolveMax(maxExpression);

			var fromTableSql = FormatTableName();

			var whereSql = ResolveExpression.ResolveWhereList();

			string noneSql = "";
			var joinSql = ResolveExpression.ResolveJoinSql(JoinList, ref noneSql);

			SqlString = $"{selectSql} {fromTableSql}{joinSql} {whereSql} ";

			return this;
		}

		public override SqlProvider FormatUpdateSelect<T>(Expression<Func<T, T>> updator)
		{
			var update = ResolveExpression.ResolveUpdate(updator);

			var selectSql = ResolveExpression.ResolveSelectOfUpdate(EntityCache.QueryEntity(typeof(T), this), Context.Set.SelectExpression);

			var whereSql = ResolveExpression.ResolveWhereList();
			Params.AddDynamicParams(update.Param);

			SqlString = $"UPDATE {FormatTableName(false, false)} {update.SqlCmd} {selectSql} {whereSql}";

			return this;
		}

		public override SqlProvider CreateNew()
		{
			return new PostgreSqlProvider();
		}

		///// <summary>
		///// 获取批量插入-SQL
		///// </summary>
		///// <typeparam name="T"></typeparam>
		///// <param name="conn"></param>
		///// <param name="list"></param>
		///// <returns></returns>
		//public override SqlProvider ExcuteBulkCopy<T>(NpgsqlConnection conn, IEnumerable<T> list)
		//{
		//	var dt = list.ToDataTable();

		//	if (conn.State == NpgsqlConnection.Closed)
		//		conn.Open();

		//	using (var sqlbulkcopy = new SqlBulkCopy((SqlConnection)conn))
		//	{
		//		sqlbulkcopy.DestinationTableName = dt.TableName;
		//		for (var i = 0; i < dt.Columns.Count; i++)
		//		{
		//			sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
		//		}
		//		sqlbulkcopy.WriteToServer(dt);
		//	}

		//	return this;
		//}
	}
}
